--作业：(满分：100分)
--1.建表：(20分)
CREATE TABLE area_table(
    store_id varchar(10),
    area varchar(20),
    leader_id int
)

CREATE TABLE store_table(
    store_id varchar(10),
    order_id varchar(12),
    sales_volume int,
    salesdate date
)

insert into area_table values
('store1','alpha',139844),('store2','alpha',44311),('store3','alpha',951837),('store4','alpha',298987),
('store5','beta',774548),('store6','beta',974663),('store7','beta',550242),('store8','beta',735906),('store9','beta',813931),('store10','beta',269966),('store11','beta',442066),
('store12','gamma',52404),('store13','gamma',250697),('store14','gamma',733530),('store15','gamma',356103),('store16','gamma',770287)

insert into store_table values()
同上，但采取数据导入形式

2.统计(20分)
2.1.统计每日每个店铺的销量(10分)
select salesdate,store_id,sum(sales_volume) from store_table
group by sales_volumes,store_id

2.2.统计每日每个区域的销量(10分)
select a.salesdate,b.area,sum(a.sales_volume) as '每日每个店铺销量'
from store_table a 
left join  area_table b 
on a.store_id=b.store_id
group by a.salesdate,b.area

3.程序异常(20分)
3.1.请分析代码为什么无法执行出期望结果？(10分)
where条件筛选store_id的数据date符合昨天日期数据，自动过滤掉store3的数据没有返回值
3.2.请修改代码，产出报表的设想结果。(10分)
select a.store_id,IFNULL(sum(sales_volume),0) as sales_volumes
from area_table a
left outer join 
(select store_id,sales_volume from store_table b
where salesdate=DATE_SUB(curdate(),INTERVAL 1 DAY))b
on a.store_id=b.store_id
where a.area='alpha' 
GROUP BY a.store_id
order by a.store_id;

二、SQL语句阅读能力(40分)
1.from kps_dwd.kps_dwd_dd_view_user_active
2.where dt='${dt_1}'
3.select dt,product_id,st_time,device_id
4.from (...)a0
5.left join  
(select
 dt
 ,mkey
 ,device_id
 from kps_dwd.kps_dwd_dd_user_channels
 where dt='${dt_1}'
 ) a1 on a0.device_id=a1.device_id
6.from temp_active_hour_table_kps
7.select
dt,product_id ,mkey ,hour ,device_id ,'active' as status from temp_active_hour_table_kps group by dt,mkey,product_id,device_id,hour
8.union all select dt,product_id ,mkey ,min(hour) as hour ,device_id ,'first' as status from temp_active_hour_table_kps group by dt,mkey,product_id,device_id
9.from (...)a0
10.left join select dt,device_id from kps_dwd.kps_dwd_dd_fact_view_new_user where dt='${dt_1}' group by dt,device_id 
11.from
(select dt,'K-pop' as product ,product_id ,mkey ,hour ,status ,count(distinct a.device_id) as dau ,count(distinct if(b.device_id is not null,a.device_id,null)) as new ……group by dt ,product_id ,mkey ,hour ,status ) a1
12.left join asian_channel.dict_lcmas_channel b1 on a1.mkey = b1.mkey;
13.select
dt,product ,product_id ,a1.mkey ,name_cn ,hour ,status ,dau ,new